package visiopuce.DAO;

import java.math.BigDecimal;
import java.util.List;

import org.hibernate.Query;

import visiopuce.HibernateUtil;
import visiopuce.objets.Alteration;

public class AlterationDAOImpl extends GenericDAOImpl<Alteration, BigDecimal> implements AlterationDAO {

	/** Constructeur privé */
	private AlterationDAOImpl() {
	}

	/** Instance unique non préinitialisée */
	private static AlterationDAOImpl INSTANCE = null;

	/** Point d'accès pour l'instance unique du singleton */
	public static AlterationDAO getInstance() {
		if (INSTANCE == null) {
			INSTANCE = new AlterationDAOImpl();
		}
		return INSTANCE;
	}

	public List<Alteration> findAllByidPuce(Integer idPuce) {
		List<Alteration> alterations = null;
		String sql = "SELECT a FROM Alteration a WHERE a.puce.idPuce = :idPuce";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("idPuce", idPuce);
		alterations = findMany(query);
		return alterations;
	}

	@Override
	public List<Alteration> getCNVChevauchant(Alteration alteration, int seuil) {
		List<Alteration> alterations = null;
		Long debut = Math.max(0, alteration.getDebutHg19() - seuil);
		Long fin = alteration.getFinHg19() + seuil;
		String sql = "SELECT a FROM Alteration a WHERE a.chromosome like :chr AND a.debutHg19 < :fin AND a.finHg19 > :debut AND a.puce.typePuce.idTypePuce != 1 ";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("chr", alteration.getChromosome());
		query.setParameter("fin", fin);
		query.setParameter("debut", debut);
		alterations = findMany(query);
		return alterations;

	}

	@Override
	public List getCNVChevauchant4Graph(Alteration alteration, int seuil) {
	//	List<Alteration> alterations = null;
		Long debut = Math.max(0, alteration.getDebutHg19() - seuil);
		Long fin = alteration.getFinHg19() + seuil;
		//String sql = "SELECT a, p.sexe, p.patient, pr.numeroPrelevement, u.idPuce, u.typePuce.libelle, u.typePuce.marquePuce.libelle, pr.indication.libelle FROM  Alteration a, Personne p, Puce u, Prelevement pr WHERE a.puce.idPuce = u.idPuce and u.analyse.prelevement.idPrelevement = pr.idPrelevement and pr.personne.idPersonne = p.idPersonne and a.chromosome like :chr AND a.debutHg19 < :fin AND a.finHg19 > :debut AND a.puce.typePuce.idTypePuce != 1 ";
		
		
		String sql = "SELECT a, a.puce.analyse.prelevement.numeroPrelevement, a.puce.typePuce.libelle, a.puce.typePuce.marquePuce.libelle, a.puce.analyse.prelevement.indication.libelle, a.puce.analyse.prelevement.idPrelevement, a.puce.analyse.prelevement.personne.phenotype, a.puce.analyse.prelevement.personne.idPersonne, a.puce.analyse.prelevement.secteur.libelle, ABS (a.debutHg19 - :debut) +   ABS(a.finHg19 -:fin) as ressemblance FROM  Alteration a WHERE a.chromosome like :chr AND a.debutHg19 < :fin AND a.finHg19 > :debut AND a.puce.typePuce.idTypePuce != 1 AND a.manuel = false ORDER by ressemblance";
		
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("chr", alteration.getChromosome());
		query.setParameter("fin", fin);
		query.setParameter("debut", debut);
		List t =  query.list();
	//	alterations = findMany(query);
		return t;

	}
	
	
	
	@Override
	public boolean cnvExiste(Alteration alt) {

		String sql = "SELECT count (a.idAlteration) FROM Alteration a WHERE a.chromosome like :chr AND a.debutHg19 = :debutHg19 AND a.finHg19 = :finHg19 AND a.puce.idPuce = :idPuce";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("chr", alt.getChromosome());
		query.setParameter("debutHg19", alt.getDebutHg19());
		query.setParameter("finHg19", alt.getFinHg19());
		query.setParameter("idPuce", alt.getPuce().getIdPuce());
		Long nbAlt = (Long) query.uniqueResult();

		return (nbAlt > 0);
	}

	@Override
	public boolean estCNPc(Alteration alt) {
		String sql = "select c from CNPc as c where chromosome = :chr and nb_copie = :cn_statut_calc and (c.debutHg19-20 <= :debutAlt1 AND c.finHg19 >= :debutAlt2 " + "AND  c.finHg19 + 20 >= :finAlt1 AND c.debutHg19 <= :finAlt2 )";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("chr", alt.getChromosome());
		query.setParameter("cn_statut_calc", alt.getNbCopie());
		query.setParameter("debutAlt1", alt.getDebutHg19());
		query.setParameter("debutAlt2", alt.getDebutHg19());
		query.setParameter("finAlt1", alt.getFinHg19());
		query.setParameter("finAlt2", alt.getFinHg19());
		List liste = query.list();
		return (liste.size() > 0);
	}

	@Override
	public List<Alteration> getCNVFormule(Integer idPuce) {
		List<Alteration> alterations = null;
		String sql = "SELECT distinct a FROM Alteration a WHERE a.puce.idPuce = :idPuce AND (a.aVerifBioMol is true OR a.aVerifFish is true OR a.prFormule is true OR a.annotationFinale like 'VOUS' OR a.annotationFinale like 'pathologique') ";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("idPuce", idPuce);
		alterations = findMany(query);
		return alterations;
	}

	@Override
	public List<Alteration> getAlterationAVerifqPCR(Integer idPuce) {
		List<Alteration> alterations = null;
		String sql = "SELECT a FROM Alteration a WHERE  a.aVerifBioMol is true and  a.puce.idPuce = :idPuce ";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("idPuce", idPuce);
		alterations = findMany(query);
		return alterations;
	}

	@Override
	public List<Alteration> getAlterationAVerifFISH(Integer idPuce) {
		List<Alteration> alterations = null;
		String sql = "SELECT a FROM Alteration a WHERE  a.aVerifFish is true and  a.puce.idPuce = :idPuce ";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("idPuce", idPuce);
		alterations = findMany(query);
		return alterations;
	}

	@Override
	public List<Alteration> getAlterationAVerif(Integer idPuce) {
		List<Alteration> alterations = null;
		String sql = "SELECT a FROM Alteration a WHERE  (a.aVerifFish is true OR a.aVerifBioMol is true) and  a.puce.idPuce = :idPuce ";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("idPuce", idPuce);
		alterations = findMany(query);
		return alterations;
	}

	@Override
	public Long nbAltByidPuce(Integer idPuce) {
		List<Alteration> alterations = null;
		String sql = "SELECT count(*) FROM Alteration a WHERE a.puce.idPuce = :idPuce";
		Query query = HibernateUtil.getSession().createQuery(sql).setParameter("idPuce", idPuce);
		Long count = (Long)query.uniqueResult();
		return count;
	}

	
	//
	// public function getCNVBaseByBornes($chromosome, $debutAlt, $finAlt, $hg,
	// $idGroupe, $idPuce = null, $debut = null, $fin = null,
	// $idAutrePucePatient = null) {
	// $stringR = "";
	// if ($hg == "hg18") {
	// $query =
	// "SELECT DISTINCT BM.categorie as catBiomed, BM.libelle as libBiomed, B.idalteration, B.chromosome, B.idPuce, B.cn_statut, B.debut, B.fin, I.phenotype, B.mozaic, I.estPatient, I.idPersonne, I.sexe, B.estCNPCalc, A.numero, A.causale, "
	// .
	// "G.abrev as abrevGroupe, O.idPrelevement, P.idType_puce, RP.formule_chr, VBM.idVerifBioMol, VBM.detecte as detecteBM ,VF.idVerifFish, VF.detecte as detecteF ";
	// } else {
	// $query =
	// "SELECT DISTINCT BM.categorie as catBiomed, BM.libelle as libBiomed, B.idalteration, B.chromosome, B.idPuce, B.cn_statut, B.debutHg19, B.finHg19, I.phenotype, B.mozaic, I.estPatient, I.idPersonne, I.sexe, B.estCNPCalc, A.numero, A.causale, "
	// .
	// "G.abrev as abrevGroupe, O.idPrelevement, P.idType_puce, RP.formule_chr, VBM.idVerifBioMol, VBM.detecte as detecteBM ,VF.idVerifFish, VF.detecte as detecteF ";
	// }
	//
	// $query .= " FROM Puce AS P ";
	// $query .=
	// " JOIN Prelevement AS O on P.idPrelevement = O.idPrelevement JOIN Personne AS I on O.idPersonne = I.idPersonne join Groupe as G on G.idGroupe = I.idGroupe ";
	// $query .=
	// " left outer join biomedecine AS BM on BM.idBiomedecine = I.idBiomedecine ";
	// $query .= " left outer join rendu_puce AS RP on RP.idPuce = P.idPuce ";
	// $query .=
	// " JOIN Alteration AS B on B.idPuce = P.idPuce left outer join Annotation as A on A.idalteration = B.idalteration ";
	// $query .=
	// " left outer join verifbiomol as VBM on B.idalteration = VBM.idalteration ";
	// $query .=
	// " left outer join verifFish as VF on B.idalteration = VF.idalteration ";
	// $query .= " WHERE 1 ";
	// if ($hg == "hg18") {
	//
	//
	//
	// if ($idPuce != null) {
	// $query .= " AND (I.idPucePrincipale = P.idPuce or P.idPuce = :idPuce"; //
	// . intval ( anti_injection ( $idPuce ) );
	// } else {
	// $query .= " AND (I.idPucePrincipale = P.idPuce";
	// }
	// if ($idAutrePucePatient != null && trim ( $idAutrePucePatient ) != ")") {
	// $query .= " OR P.idPuce in " . $idAutrePucePatient . "";
	// }
	// $query .= ") AND P.idType_puce != 1 ";
	// /*
	// * if ($idGroupe > 0){ $query .=
	// " AND I.idGroupe = ".mysql_real_escape_string($idGroupe); }
	// */
	//
	// $query .=
	// " AND (B.publier = '1' OR (B.publier != '1' AND I.idGroupe = :idGroupe )";
	// // . intval ( anti_injection ( $idGroupe ) ) . ") ";
	//
	// $query .= ") ORDER BY A.numero DESC";
	//
	// $prep = $this->AccesDonnees->prepare ( $query );
	// $prep->bindValue ( ":chr", $chromosome, PDO::PARAM_STR );
	// if (! is_null ( $debut )) {
	// $prep->bindValue ( ":debut", $debut, PDO::PARAM_INT );
	// }
	// if (! is_null ( $fin )) {
	// $prep->bindValue ( ":fin", $fin, PDO::PARAM_INT );
	// }
	// if ($idPuce != null) {
	// $prep->bindValue ( ":idPuce", $idPuce, PDO::PARAM_INT );
	// }
	// $prep->bindValue ( ":idGroupe", $idGroupe, PDO::PARAM_INT );
	//
	// $prep->execute ();
	//
	// if ($prep->rowCount () == 0) {
	// $stringR = "";
	// } else {
	//
	// $alterations = array ();
	// $tailles = array ();
	// $strings = array ();
	// $idAlts = "::";
	// $i = 0;
	// $TYPE_PUCE_DAO = new TypePuceDAO ();
	// while ( $r = $prep->fetch () ) {
	// // stripslashes($r["phenotype"]);
	// if (isset ( $r ["estCNPCalc"] ) && ! is_null ( $r ["estCNPCalc"] ) && $r
	// ["estCNPCalc"] != "") {
	// $estCNPCalc = $r ["estCNPCalc"];
	// } else {
	// $estCNPCalc = 0;
	// }
	// if (isset ( $r ["causale"] ) && ! is_null ( $r ["causale"] ) && $r
	// ["causale"] != "") {
	// $causale = $r ["causale"];
	// } else {
	// $causale = 6;
	// }
	//
	// $typePuce = $TYPE_PUCE_DAO->rechercher_typePuce ( $r ["idType_puce"] );
	// if ($typePuce != null && $typePuce->libelle ==
	// "Cytogenetics Whole-Genome 2.7M") {
	// $t = "2.7M";
	// } else {
	// if ($typePuce->libelle == "SurePrint G3 Human CGH Microarray 4x180K") {
	// $t = "4x180K";
	// if ($r ["numero"] != 4 && $r ["numero"] != 3) {
	// $causale = 6;
	// }
	// } else {
	// $t = $typePuce->libelle;
	// }
	// }
	// $type = substr ( $typePuce->MarquePuce->libelle, 0, 3 ) . '.' . $t;
	// $PHENOTYPE_DAO = new PhenotypeDAO ();
	// if (! isset ( $r ["phenotype"] ) || $r ["phenotype"] == null || trim ( $r
	// ["phenotype"] ) == "") {
	// $r ["phenotype"] = "";
	// }
	// $phentoypes = "**";
	// if ($r ["catBiomed"] == 1) {
	// $phentoypes .= "Prénat. : ";
	// } else if ($r ["catBiomed"] == 2) {
	// $phentoypes .= "Postnat. : ";
	// } else if ($r ["catBiomed"] == 3) {
	// $phentoypes .= "Foeto. : ";
	// }
	// if ($r ["libBiomed"] != null) {
	// $phentoypes .= $r ["libBiomed"];
	// }
	// $phentoypes .= "**";
	// $phentoypes .= stripslashes (
	// $PHENOTYPE_DAO->rechercher_phenotypeByidPesonne ( $r ["idPersonne"], $r
	// ["phenotype"] ) );
	//
	// if (strstr ( $idAlts, "::" . $r ["idalteration"] . "::" ) === FALSE) {
	// $idAlts .= $r ["idalteration"] . "::";
	// if ($hg == "hg18" && $r ["debut"] != NULL && $r ["debut"] != "" && $r
	// ["fin"] != NULL && $r ["fin"] != "") {
	// $diff = abs ( $r ["debut"] - $debutAlt ) + abs ( $r ["fin"] - $finAlt );
	// $tailles [$i] = $diff;
	// $strings [$i] = $r ["debut"] . "::" . $r ["fin"] . "::" . $r
	// ["idalteration"] . "::" . $r ["idPuce"] . "::" . $this->calculeCN_State (
	// $r ["cn_statut"], $r ["chromosome"], $r ["sexe"] ) . "::" . $phentoypes .
	// "::" . $r ["estPatient"] . "::" . $r ["idPersonne"] . "::" . $r ["sexe"]
	// . "::" . $estCNPCalc . "::" . $causale . "::" . $r ["abrevGroupe"] . "::"
	// . $type . "::" . $r ["mozaic"] . ":: " . $r ["formule_chr"] . ":: " . $r
	// ["detecteBM"] . ":: " . $r ["detecteF"] . ":: " . $r ["idVerifBioMol"] .
	// ":: " . $r ["idVerifFish"] . ";;";
	// } else if ($hg == "hg19" && $r ["debutHg19"] != NULL && $r ["debutHg19"]
	// != "" && $r ["finHg19"] != NULL && $r ["finHg19"] != "") {
	// $diff = abs ( $r ["debutHg19"] - $debutAlt ) + abs ( $r ["finHg19"] -
	// $finAlt );
	// $tailles [$i] = $diff;
	// $strings [$i] = $r ["debutHg19"] . "::" . $r ["finHg19"] . "::" . $r
	// ["idalteration"] . "::" . $r ["idPuce"] . "::" . $this->calculeCN_State (
	// $r ["cn_statut"], $r ["chromosome"], $r ["sexe"] ) . "::" . $phentoypes .
	// "::" . $r ["estPatient"] . "::" . $r ["idPersonne"] . "::" . $r ["sexe"]
	// . "::" . $estCNPCalc . "::" . $causale . "::" . $r ["abrevGroupe"] . "::"
	// . $type . "::" . $r ["mozaic"] . ":: " . $r ["formule_chr"] . ":: " . $r
	// ["detecteBM"] . ":: " . $r ["detecteF"] . ":: " . $r ["idVerifBioMol"] .
	// ":: " . $r ["idVerifFish"] . ";;";
	// }
	//
	// $i ++;
	// }
	// }
	// array_multisort ( $tailles, SORT_NUMERIC, $strings );
	// foreach ( $strings as $string ) {
	// $stringR .= $string;
	// }
	// }
	// $prep->closeCursor ();
	// $prep = NULL;
	// return $stringR;
	// }
	// return null;
	// }

}